CREATE OR REPLACE EDITIONABLE PACKAGE BODY "SCOTT"."PKG_CONCEPT_BOARD" AS

  /*---------------------------- 海量地向表MDL_TOP_CNCPT_BOARD_IDX_DETAIL中插入数据 --------------------------------*/
  procedure WRITE_MDL_TOP_C_B_I_DETAIL(p_begin_date in varchar2,
                                       p_end_date   in varchar2) as
    -- 表示transaction_date字段
    v_transaction_date date;
    -- 某一个概念板块的交易记录数
    v_concept_board_index_data_num number;
    -- 概念板块代码
    v_concept_board_index_code varchar2(50);
    -- 当前日期的概念板块收盘价
    v_current_close_price number;
    -- 第一个日期的概念板块收盘价
    v_first_close_price number;
    -- 用于记录最近5, 10, 20个交易日内的涨幅最大的概念板块
    v_period5_up_down_percentage  number;
    v_period10_up_down_percentage  number;
    v_period20_up_down_percentage  number;
    -- 表示概念板块名称是否存在，1表示存在
    v_cncpt_board_idx_name_exist number;
    -- 概念板块名称
    v_concept_board_index_name varchar2(50);
    -- 表示表concept_board_index中的所有transaction_date字段，不能有重复，并且升序排列
    cursor cur_distinct_transaction_date is
      select distinct t.transaction_date
        from concept_board_index t
       where t.transaction_date between to_date(p_begin_date, 'yyyy-mm-dd') and
             to_date(p_end_date, 'yyyy-mm-dd')
       order by t.transaction_date asc;
    -- 在某一日的所有概念板块的代码
    cursor cur_cncpt_board_idx_by_date is
      select distinct t.code
        from concept_board_index t
       where t.transaction_date = v_transaction_date;
  begin
    for i in cur_distinct_transaction_date loop
      v_transaction_date := i.transaction_date;
      for j in cur_cncpt_board_idx_by_date loop
        v_concept_board_index_code := j.code;
        -- 重置变量
        v_period5_up_down_percentage  := null;
        v_period10_up_down_percentage  := null;
        v_period20_up_down_percentage  := null;
        v_concept_board_index_data_num   := null;
        v_current_close_price          := null;
        v_first_close_price            := null;
        v_cncpt_board_idx_name_exist             := null;
        v_concept_board_index_name                   := null;
      
        -- 计算某只概念板块在某个日期后的交易记录数
        select count(*)
          into v_concept_board_index_data_num
          from concept_board_index t
         where t.code = v_concept_board_index_code
           and t.transaction_date <= v_transaction_date;
      
        -- 计算v_current_close_price
        select t1.close_price
          into v_current_close_price
          from (select *
                  from concept_board_index t
                 where t.code = v_concept_board_index_code
                   and t.transaction_date <= v_transaction_date
                 order by t.transaction_date desc) t1
         where rownum <= 1;
      
        -- 计算period_type为5时的up_down_percentage
        if v_concept_board_index_data_num >= 5 then
          -- 计算v_first_close_price
          select t2.close_price
            into v_first_close_price
            from (select *
                    from (select *
                            from (select *
                                    from (select *
                                            from concept_board_index t
                                           where t.code = v_concept_board_index_code
                                             and t.transaction_date <= v_transaction_date
                                           order by t.transaction_date desc)
                                   where rownum <= 5) t1
                           order by t1.transaction_date asc)
                   where rownum <= 1) t2;
          v_period5_up_down_percentage := (v_current_close_price -
                                           v_first_close_price) /
                                           v_first_close_price * 100;
        end if;
      
        -- 计算period_type为10时的up_down_percentage
        if v_concept_board_index_data_num >= 10 then
          -- 计算v_first_close_price
          select t2.close_price
            into v_first_close_price
            from (select *
                    from (select *
                            from (select *
                                    from (select *
                                            from concept_board_index t
                                           where t.code = v_concept_board_index_code
                                             and t.transaction_date <= v_transaction_date
                                           order by t.transaction_date desc)
                                   where rownum <= 10) t1
                           order by t1.transaction_date asc)
                   where rownum <= 1) t2;
          v_period10_up_down_percentage := (v_current_close_price -
                                           v_first_close_price) /
                                           v_first_close_price * 100;
        end if;
      
        -- 计算period_type为20时的up_down_percentage
        if v_concept_board_index_data_num >= 55 then
          -- 计算v_first_close_price
          select t2.close_price
            into v_first_close_price
            from (select *
                    from (select *
                            from (select *
                                    from (select *
                                            from concept_board_index t
                                           where t.code = v_concept_board_index_code
                                             and t.transaction_date <= v_transaction_date
                                           order by t.transaction_date desc)
                                   where rownum <= 20) t1
                           order by t1.transaction_date asc)
                   where rownum <= 1) t2;
          v_period20_up_down_percentage := (v_current_close_price -
                                           v_first_close_price) /
                                           v_first_close_price * 100;
        end if;
      
        -- 查询概念板块名称
        select count(*)
          into v_cncpt_board_idx_name_exist
          from concept_board t
         where t.code = v_concept_board_index_code;
        if v_cncpt_board_idx_name_exist = 1 then
          select t.name
            into v_concept_board_index_name
            from concept_board t
           where t.code = v_concept_board_index_code;
        end if;
      
        -- 向表mdl_top_cncpt_board_idx_detail中插入数据
        insert into mdl_top_cncpt_board_idx_detail
          (transaction_date,
           code,
           name,
           up_down_percentage_5,
           up_down_percentage_10,
           up_down_percentage_20)
        values
          (v_transaction_date,
           v_concept_board_index_code,
           v_concept_board_index_name,
           v_period5_up_down_percentage,
           v_period10_up_down_percentage,
           v_period20_up_down_percentage);
        commit;
      end loop;
    end loop;
  end WRITE_MDL_TOP_C_B_I_DETAIL;

END PKG_CONCEPT_BOARD;